<?
/**
 *
 * 'mysqli' class
 *
 * Extension to 'mysqli' class with various convinience functions in
 * particular to make it easier to use prepared statements.
 *
 *
 * LICENSE: GPL Version 2
 *
 * @copyright	2006 SANS Institute
 * @license		http://www.gnu.org/copyleft/gpl.html
 * @version 	$id$
 * @since 		File available since Release 1
 * @package     mysql
 */
class db extends mysqli {
    protected $aResult;
    protected $oMetaData;
    /**
	 * constructor just called the parent constructor with the right paramters
	 *
	 * @param array $aSQL array with database parameters.
	 */
    function __construct($aSQL) {
      parent::__construct($aSQL['host'],$aSQL['user'],$aSQL['password'],$aSQL['database'],$aSQL['port']);
      if (mysqli_connect_errno()) {
        header("Location: /index_cached.html");
        exit();
      }
    }
    /**
	 * Prepares and executes a Query.
	 *
	 * @param string $sQuery the query to be executed.
	 * @param string $sTypes string defining the type of the parameters.
	 * @param array $aValues these values will be bound to the parameters.
	 * @return object returns the prepared/executed mysql statement
	 */
    function prepandexec($sQuery,$sTypes='',$aValues='',$bDebug=FALSE) {
      
        $sField='';
        if ( $bDebug ) {
          dprint("prep $sQuery");
        }
        unset($oStmt);
        
        $oStmt=$this->prepare($sQuery);
        if ( ! $oStmt ) {
          dprint("ERROR");
          dprint($sQuery);
          dprint($this->error);
          dprint($_SERVER);
            throw new Exception("Prepare failed for $sQuery in 'class.mysql.php::prepandexec' Line ".__LINE__." ".$this->error);
        }
        if ( $bDebug ) {
          dprint("prepared $sQuery");
        }
        if ( $sTypes!='') {
            $aParam[0]=$oStmt;
            $aParam[1]=$sTypes;
            if ( is_array($aValues)) {
                foreach($aValues as $sValue) {

                    array_push($aParam,$sValue);
                }} else {

                    $aParam[2]=$aValues;
                }

                if ( ! call_user_func_array('mysqli_stmt_bind_param',$aParam)) {
                    throw new Exception("mysqli_stmt_bind_param failed for $sQuery in
				'class.mysqlphp::prepandexec' Line
				".__LINE__." ".$this->error);
                }
        }
        if ( ! $oStmt->execute() ) {
            throw new Exception("Execute failed for $sQuery in
				'class.mysqlphp::prepandexec' Line
				".__LINE__." ".$this->error);
            if ( $bDebug ) {
              dprint("execution failed ".$this->error);
            }
        } else {
          if ( $bDebug ) {
            dprint("executed query ".$oStmt->affected_rows." rows affected" );
          }
        }

        return $oStmt;
    }
    /**
	 * Fetches values and throws them into an array
	 *
	 * @param object prepared and executed sql statments.
     * typically from prepandexec
	 * @return array result of the query
	 */
    function fetcharray($oStmt) {
        $oMetaData=$oStmt->result_metadata();
        $nCount=1;
        $aField[0]=&$oStmt;
        while($sField=$oMetaData->fetch_field()) {
            $aField[$nCount]=&$array[$sField->name];
            $nCount++;
        }
        $oStmt->store_result();
        call_user_func_array('mysqli_stmt_bind_result',$aField);
        $oStmt->fetch();
        return $array;

    }
    /**
	 * replace a row in a table
	 *
	 * @param string nanme of the table
	 * @param array new row
	 */
    function replace($sTable,$aRow) {
        $this->rowcommand($sTable,$aRow,'replace');
    }
    /**
     * this function is a helper used to execute commands that affect multiple
     * columns in a row. Typically used for 'insert' and 'replace'.
     *
     * @param string $sTable
     * @param array $aRow
     * @param string $sCommand
     */
    function rowcommand($sTable,$aRow,$sCommand) {
        $sType='';
        $aKeys=array();
        $aQs=array();
        foreach($aRow as $sKey=>$sValue) {
            array_push($aKeys,$sKey);
            array_push($aQs,'?');
            if ( is_bool($sValue) ) {
                $sType.='b';
                continue;
            }
            if ( is_int($sValue) ) {
                $sType.='i';
                continue;
            }
            if ( is_numeric($sValue) ) {
                $sType.='d';
                continue;
            }
            $sType.='s';
        }
        $sCols=join(',',$aKeys);
        $sQ=join(',',$aQs);
        return $this->prepandexec("$sCommand into $sTable ($sCols) values ($sQ)",$sType,array_values($aRow));
    }
    /**
     * insert a row in a table
     *
     * @param string $sTable
     * @param array $aRow
     */
    function insert($sTable,$aRow,$bDebug=FALSE) {
      if ( $bDebug ) {
        print "insert into $sTable<br>";
      }
        return $this->rowcommand($sTable,$aRow,'insert',$bDebug);
    }
  function delete($sTable,$aFields,$bDebug=FALSE) {
    $aValues=array();
    $aConditions=array();
    $sType='';
    foreach ( $aFields as $sKey=>$sValue ) {
      array_push($aConditions,"$sKey=?");
      array_push($aValues,$sValue);
      if ( is_bool($sValue) ) {
        $sType.='b';
        continue;
      }
      if ( is_int($sValue) ) {
        $sType.='i';
        continue;
      }
      if ( is_numeric($sValue) ) {
        $sType.='d';
        continue;
      }
      $sType.='s';
    }
    $sQuery="delete from $sTable where ".join(' and ',$aConditions);
    if ( $bDebug ) {
      dprint($sQuery);
      dprint($aConditions);
      dprint($aValues);
    }
    return $this->prepandexec($sQuery,$sType,$aValues);
  }
  function update($sTable,$aRow,$aKeys,$bDebug=FALSE) {
        $sType='';
        $aSets=array();
        $aWheres=array();
        $aParam=array();
        foreach($aRow as $sKey=>$sValue) {
            array_push($aSets,"$sKey=?");
            array_push($aParam,$sValue);
            if ( is_bool($sValue) ) {
                $sType.='b';
                continue;
            }
            if ( is_int($sValue) ) {
                $sType.='i';
                continue;
            }
            if ( is_numeric($sValue) ) {
                $sType.='d';
                continue;
            }
            $sType.='s';
        }
        foreach($aKeys as $sKey=>$sValue) { 
          array_push($aWheres,"$sKey=?"); 
          array_push($aParam,$sValue);
            if ( is_bool($sValue) ) {
                $sType.='b';
                continue;
            }
            if ( is_int($sValue) ) {
                $sType.='i';
                continue;
            }
            if ( is_numeric($sValue) ) {
                $sType.='d';
                continue;
            }
            $sType.='s';
        }          
        $sSet=join(',',$aSets);
        $sWhere=join(' and ',$aWheres);
        if ( $bDebug) {
          dprint($aParam);
        }
        return $this->prepandexec("update $sTable set $sSet where $sWhere",$sType,$aParam,$bDebug);
    }
    /**
	 * query that returns one simple scalar.
	 *
	 * @param string $sQuery query
	 * @param string $sTypes type string for prepare
	 * @param array $aValues values to be bound to the parameters
	 * @return string result value.
	 */
    function simple_query($sQuery,$sTypes='',$aValues='',$nCache=0) {
        $sResult='';
        if ( $nCache>0 ) {
            $aCache=$this->retrieve_cache($sQuery,$aValues,$nCache);
            if ( $aCache['valid']) {
                return $aCache['result'];
            }
        }
        $oStmt=$this->prepandexec($sQuery,$sTypes,$aValues);
        $oStmt->store_result();
        $oStmt->bind_result($sResult);
        $oStmt->fetch();
        if ( $nCache>0 ) {
            $this->write_cache($sQuery,$aValues,$sResult);
        }
        return $sResult;
    }
    /**
     * Retrieve a cached query result.
     *
     * @param string $sQuery
     * @param integer $nCache
     * @return mixed
     */
    function retrieve_cache($sQuery,$aValues,$nCache=CACHETIME) {

        $aReturn['valid']=false;
        $aReturn['result']='';
        if ( is_array($aValues) ) {
          $sValues=join('|',$aValues);
        } else {
          $sValues=$aValues; 
        }
        $sQuery.=$sValues;
        $sMD=md5($sQuery);
        $sDir=substr($sMD,-1);
        if ( ! is_dir(CACHE.$sDir) ) {
          return($aReturn);
        }
        $sFile=CACHE.'/'.eregi_replace('[^a-z0-9]','',$sQuery);
        $sFile.=md5($sQuery);


        if ( ! file_exists($sFile)) {
          return($aReturn);
        }
        if ( filectime($sFile)<time()-$nCache) {
            unlink($sFile);
            return($aReturn);
        }
        $aReturn['result']=unserialize(file_get_contents($sFile,false));
        $aReturn['valid']=true;
        return($aReturn);

    }
    /**
     * Write a result to file to later be able to retrieve it via retrieve
     * cache.
     *
     * @param string $sQuery
     * @param mixed $aResult
     */
    function write_cache($sQuery,$aValues,$aResult) {
        $sResult=serialize($aResult);
        if ( $sResult=='' ) {
          exit();
        }        
        if ( is_array($aValues) ) {
          $sValues=join('|',$aValues);
        } else {
          $sValues=$aValues; 
        }
        $sQuery.=$sValues;
        $sMD=md5($sQuery);
        $sDir=substr($sMD,-1);
        if ( ! is_dir(CACHE.$sDir) ) {
          mkdir(CACHE.$sDir);
        }
        $sFile=CACHE.$sDir.'/'.eregi_replace('[^a-z0-9]','',$sQuery);
        $sFile.=md5($sQuery);
        file_put_contents($sFile,$sResult);
    }
    /**
	 * query that returns a simple array (one row)
	 *
	 * @param unknown_type $sQuery
	 * @param unknown_type $sTypes
	 * @param unknown_type $aValues
	 * @return unknown
	 */
    function array_query($sQuery,$sTypes='',$aValues='',$nCache=0) {
        if ( $nCache>0) {
            $aCache=$this->retrieve_cache($sQuery,$aValues,$nCache);

            if ( $aCache['valid']) {
                return $aCache['result'];
            }
        }
        $oStmt=$this->prepandexec($sQuery,$sTypes,$aValues);
        $this->aResult=$this->fetcharray($oStmt);
        $oStmt->close();
        if ( $nCache>0 ) {
            $this->write_cache($sQuery,$aValues,$this->aResult);
        }
        return $this->aResult;
    }
    /**
	 * query that returns one column (multiple rows)
	 *
	 * @param string $sQuery sql query
	 * @param string $sTypes type string to bind parameters
	 * @param array $aValues values to be bound to the query
	 * @return array results
	 */
    function column_query($sQuery,$sTypes='',$aValues='',$nCache=0) {
        $sResult='';
        $aResult=array();
        if ( $nCache>0) {
            $aCache=$this->retrieve_cache($sQuery,$aValues,$nCache);
            if ( $aCache['valid']) {
                return $aCache['result'];
            }
        }
        $oStmt=$this->prepandexec($sQuery,$sTypes,$aValues);
        $oStmt->store_result();
        $oStmt->bind_result($sResult);
        while ( $oStmt->fetch()){
            array_push($aResult,$sResult);
        }
        if ( $nCache>0) {
            $this->write_cache($sQuery,$aValues,$aResult);
        }
        return $aResult;
    }
    /**
	 * queries reutrns two columns taht are returned as an associative array (hash)
	 *
	 * @param unknown_type $sQuery
	 * @param unknown_type $sTypes
	 * @param unknown_type $aValues
	 * @return unknown
	 */
    function twocolumn_query($sQuery,$sTypes='',$aValues='',$nCache=0) {
        $sResult='';
        $sValue='';
        $sKey='';
        $aResult=array();
        if ( $nCache>0) {
            $aCache=$this->retrieve_cache($sQuery,$aValues,$nCache);
            if ( $aCache['valid']) {
                return $aCache['result'];
            }
        }
        $oStmt=$this->prepandexec($sQuery,$sTypes,$aValues);
            $oStmt->store_result();
        $oStmt->bind_result($sKey,$sValue);
        while ( $oStmt->fetch()){
            $aResult[$sKey]=$sValue;
        }
        if ( $nCache>0) {
            $this->write_cache($sQuery,$aValues,$aResult);
        }
        return $aResult;
    }
    /**
	 * queries that will return multiple rows with multiple values per row. One column is picked as a key
	 *
	 * @param string $sQuery
	 * @param string $sTypes
	 * @param array $aValues
	 * @return array
	 */
    /**
	 * queries that will return multiple rows with multiple values per row. One column is picked as a key
	 *
	 * @param string $sQuery
	 * @param string $sTypes
	 * @param array $aValues
	 * @return array
	 */
    function multicolumn_query($sQuery,$sTypes='',$aValues='',$nCache=0) {
        $aResult=array();
        if ( $nCache>0) {
            $aCache=$this->retrieve_cache($sQuery,$aValues,$nCache);
            if ( $aCache['valid']) {
                return $aCache['result'];
            }
        }
        $aRow=array();
        $aBind=array();
        $oStmt=$this->prepandexec($sQuery,$sTypes,$aValues);
        $oStmt->store_result();
        $nCount=$oStmt->field_count;
        $aBind[0]=$oStmt;
        for ($i=1;$i<=$nCount;$i++) {
            $aRow[$i-1]=0;
            $aBind[$i]=&$aRow[$i-1];
        }
        call_user_func_array('mysqli_stmt_bind_result',$aBind);
        while( $oStmt->fetch() ){
            $aResult[$aRow[0]]=array();
            foreach ( $aRow as $sValue ) {
                array_push($aResult[$aRow[0]],$sValue);
            }
        }
        $oStmt->close();
        if ( $nCache>0) {
            $this->write_cache($sQuery,$aValues,$aResult);
        }
        return $aResult;
    }
    function hashes_query($sQuery,$sTypes='',$aValues='',$nCache=0) {
      $aResult=array();
        if ( $nCache>0) {
            $aCache=$this->retrieve_cache($sQuery,$aValues,$nCache);
            if ( $aCache['valid']) {
                return $aCache['result'];
            }
        }
        $aRow=array();
        list($oStmt,$aRow)=$this->prep_hash_query($sQuery,$sTypes,$aValues,$nCache);
        $aRRows=array();

        $i=0;
        while( $oStmt->fetch() ){
          foreach ( $aRow as $sKey=>$sValue ) {
            $aRRows[$i][$sKey]=$sValue;
          }
          array_push($aResult,$aRRows[$i]);
          $i++;
        }
        $oStmt->close();
        if ( $nCache>0) {
            $this->write_cache($sQuery,$aValues,$aResult);
        }
        return $aResult;
    }
    /**
	 * querries and retruns one row as a hash
     *
     * @param string $sQuery SQL query
	 * @param string $sTypes type string (for prepare)
     * @param array  $aValues
     * @return array
     */
    function hash_query($sQuery,$sTypes='', $aValues='',$nCache=0) {
        if ( $nCache>0) {
            $aCache=$this->retrieve_cache($sQuery,$aValues,$nCache);
            if ( $aCache['valid']) {
                return $aCache['result'];
            }
        }
        $aRow=array();
        list($oStmt,$aRow)=$this->prep_hash_query($sQuery,$sTypes,$aValues);
        $oStmt->fetch();
        if ( $nCache>0) {
            $this->write_cache($sQuery,$aValues,$aRow);
        }
        return $aRow;
    }
    /**
	 * prepare a query, execute it and return the result as a hash.
	 *
	 * @param string $sQuery
	 * @param string $sTypes
	 * @param array $aValues
	 * @return array
	 */
    function prep_hash_query($sQuery,$sTypes='',$aValues='',$bDebug=FALSE) {
      if ( $bDebug ) {
        dprint("prep_hash_query $sQuery $sTypes");
      }
      $aResult=array();
      $aRow=array();
      $aBind=array();
      $oStmt=$this->prepandexec($sQuery,$sTypes,$aValues,$bDebug);
      if ( $bDebug ) {
        dprint("done prepandexec");
      }
        $oResult=$oStmt->result_metadata();
        $nCount=$oStmt->field_count;
      if ( $bDebug ) {
        dprint("nCount $nCount");
        dprint("aRow:");
        dprint($aRow);
      }
        $aBind[0]=$oStmt;
        for ($i=1;$i<=$nCount;$i++) {
            $oField=$oResult->fetch_field();
            if ( $bDebug ) {
              dprint("Files # $i"); 
              dprint($oField);
              dprint("field name:".$oField->name);
            }

            $aRow[$oField->name]=0;
            $aBind[$i]=&$aRow[$oField->name];
        }
            if ( $bDebug ) {
              dprint('bind');
              dprint($aBind);
            }
            $oStmt->store_result();
        if ( call_user_func_array('mysqli_stmt_bind_result',$aBind) ) {
          if ( $bDebug ) {
            dprint('bind done');
          }
          return array($oStmt,&$aRow);
        }
        throw new Exception("call_user_func_array/mysqli_stmt_bind_result failed for $sQuery in 'class.mysql.php::prep_hash_query' Line ".__LINE__." ".$this->error);        
        
    }
    /**
     * fill a query result into an HTML template. This is frequently used
     * to display tables.
     *
     * @param string $sQuery
     * @param string $sTypes
     * @param array $aValues
     * @param string $sTemplate
     * @param string $sOdd
     * @param string $sEven
     * @return string
     */
    function template_query($sQuery,$sTypes='',$aValues='',$sTemplate,$sOdd='oddrow',$sEven='evenrow',$nCache=0,$bHTMLSafe=TRUE,$bDebug=FALSE) {
      if ( $bDebug) {
        dprint("template_query $sQuery");
      }
      if ( $nCache>0) {
      if ( $bDebug) {
        dprint("template_query check cache");
      }
        $aCache=$this->retrieve_cache($sQuery,$aValues,$nCache);
        if ( $aCache['valid']) {
      if ( $bDebug) {
        dprint("template_query use cache");
      }
          return $aCache['result'];
        }
      }
      if ( $bDebug) {
        dprint("template_query no cache");
      }
        $aRow=array();
        $nCount=0;
        $aAlt[0]=$sEven;
        $aAlt[1]=$sOdd;
        $sText='';
        list($oStmt,$aRow)=$this->prep_hash_query($sQuery,$sTypes,$aValues,$bDebug);
        while ( $oStmt->fetch() ) {
          $nCount++;
          $aRow['ALTERNATE']=$aAlt[$nCount%2];
          $sText.=fill_template($aRow,$sTemplate,'%%',$bHTMLSafe);
          if ( $bDebug) {
            dprint("$nCount");
          }
        }
        if ( $nCache>0) {
          $this->write_cache($sQuery,$aValues,$sText);
        }
        return ($sText);
    }

}
/**
 * helper function to replace fields in a template with values from a hash
 *
 * @param array $hash values to use to fill in template
 * @param string $template template to use
 * @param string $delimiter delimiter used to indicate fields to be replaced.
 * @param bool $htmlsafe if true, then values are html escaped to prevent XSS.
 * @return string
 */
function fill_template($hash, $template, $delimiter='%%',$htmlsafe=true) {
    while ( list($key, $value)=each($hash) ) {
        if ( $htmlsafe ) {
            $value=htmlentities($value);
        }
        $template=ereg_replace("$delimiter$key$delimiter","$value",$template);
    }
    $template=ereg_replace("%%[^%]*%%","",$template);
    return $template;
}